package database

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	_ "github.com/godror/godror"
	"oracle-mcp-server/internal/config"
	"oracle-mcp-server/pkg/logger"
)

// DB 数据库连接管理器
type DB struct {
	conn        *sql.DB
	poolManager *PoolManager
	executor    *ConcurrentExecutor
	logger      logger.Logger
	config      *config.Config // 添加配置引用
}

// ColumnInfo 表列信息
type ColumnInfo struct {
	Field   string `json:"field"`
	Type    string `json:"type"`
	Null    string `json:"null"`
	Key     string `json:"key"`
	Default string `json:"default"`
	Extra   string `json:"extra"`
}

// New 创建新的数据库连接
func New(cfg config.DatabaseConfig) (*DB, error) {
	return NewWithFullConfig(&config.Config{Database: cfg})
}

// NewWithFullConfig 使用完整配置创建数据库连接
func NewWithFullConfig(cfg *config.Config) (*DB, error) {
	// 创建日志记录器
	log := logger.New(true) // 启用调试模式
	
	log.Info("正在初始化数据库连接...")
	
	// 构建DSN (Data Source Name) for Oracle
	var dsn string
	if cfg.Database.ConnectString != "" {
		// 使用完整的连接字符串
		dsn = fmt.Sprintf("%s/%s@%s", cfg.Database.Username, cfg.Database.Password, cfg.Database.ConnectString)
	} else if cfg.Database.ServiceName != "" {
		// 使用服务名
		dsn = fmt.Sprintf("%s/%s@%s:%d/%s", cfg.Database.Username, cfg.Database.Password, cfg.Database.Host, cfg.Database.Port, cfg.Database.ServiceName)
	} else if cfg.Database.SID != "" {
		// 使用SID
		dsn = fmt.Sprintf("%s/%s@%s:%d:%s", cfg.Database.Username, cfg.Database.Password, cfg.Database.Host, cfg.Database.Port, cfg.Database.SID)
	} else {
		return nil, NewDatabaseError("invalid_config", fmt.Errorf("必须提供ConnectString、ServiceName或SID中的一个"))
	}

	// 打开数据库连接
	conn, err := sql.Open("godror", dsn)
	if err != nil {
		log.WithError(err).Error("打开数据库连接失败")
		return nil, NewDatabaseError("open_connection", err)
	}

	// 配置连接池
	conn.SetMaxOpenConns(cfg.Database.MaxOpenConns)
	conn.SetMaxIdleConns(cfg.Database.MaxIdleConns)
	conn.SetConnMaxLifetime(cfg.Database.ConnMaxLifetime)
	conn.SetConnMaxIdleTime(cfg.Database.ConnMaxIdleTime)

	log.WithFields(map[string]interface{}{
		"max_open_conns": cfg.Database.MaxOpenConns,
		"max_idle_conns": cfg.Database.MaxIdleConns,
		"max_lifetime":   cfg.Database.ConnMaxLifetime,
		"max_idle_time":  cfg.Database.ConnMaxIdleTime,
	}).Info("数据库连接池配置完成")

	// 测试连接
	if err := conn.Ping(); err != nil {
		conn.Close()
		log.WithError(err).Error("数据库连接测试失败")
		return nil, NewDatabaseError("ping_connection", err)
	}

	log.Info("数据库连接测试成功")

	// 创建连接池管理器
	poolManager := NewPoolManager(conn, cfg.Database)

	// 创建并发执行器（默认4个工作协程）
	executor := NewConcurrentExecutor(&DB{conn: conn, poolManager: poolManager, logger: log}, 4)

	log.Info("数据库初始化完成")

	return &DB{
		conn:        conn,
		poolManager: poolManager,
		executor:    executor,
		logger:      log,
		config:      cfg,
	}, nil
}

// Close 关闭数据库连接
func (db *DB) Close() error {
	// 关闭并发执行器
	if db.executor != nil {
		db.executor.Close()
	}
	
	// 关闭数据库连接
	if db.conn != nil {
		return db.conn.Close()
	}
	return nil
}

// GetPoolStats 获取连接池统计信息
func (db *DB) GetPoolStats() *PoolStats {
	return db.poolManager.GetStats()
}

// HealthCheck 检查数据库和连接池健康状态
func (db *DB) HealthCheck(ctx context.Context) error {
	return db.poolManager.HealthCheck(ctx)
}

// OptimizePool 优化连接池配置
func (db *DB) OptimizePool() {
	db.poolManager.OptimizePool()
}

// GetExecutorStats 获取并发执行器统计信息
func (db *DB) GetExecutorStats() ExecutorStats {
	return db.executor.GetStats()
}

// SubmitAsyncQuery 提交异步查询
func (db *DB) SubmitAsyncQuery(id, query string, limit int, timeout time.Duration) <-chan QueryResult {
	return db.executor.SubmitQuery(id, query, limit, timeout)
}

// BatchExecuteQueries 批量执行查询
func (db *DB) BatchExecuteQueries(queries []BatchQuery) []QueryResult {
	return db.executor.BatchExecute(queries)
}

// ListTables 获取数据库中所有表的列表
func (db *DB) ListTables() ([]string, error) {
	db.logger.Debug("开始获取数据库表列表")
	
	query := "SELECT table_name FROM user_tables ORDER BY table_name"
	rows, err := db.conn.Query(query)
	if err != nil {
		db.logger.WithError(err).Error("执行查询表列表失败")
		return nil, NewQueryError(query, err)
	}
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var tableName string
		if err := rows.Scan(&tableName); err != nil {
			db.logger.WithError(err).Error("扫描表名结果失败")
			return nil, NewQueryError(query, err)
		}
		tables = append(tables, tableName)
	}

	if err := rows.Err(); err != nil {
		db.logger.WithError(err).Error("遍历表名结果失败")
		return nil, NewQueryError(query, err)
	}

	db.logger.WithField("table_count", len(tables)).Info("成功获取数据库表列表")
	return tables, nil
}

// DescribeTable 获取指定表的结构信息
func (db *DB) DescribeTable(tableName string) ([]ColumnInfo, error) {
	db.logger.WithField("table_name", tableName).Debug("开始获取表结构信息")
	
	// 验证表名，防止SQL注入
	if !isValidTableName(tableName) {
		db.logger.WithField("table_name", tableName).Error("无效的表名")
		return nil, NewTableError("describe_table", tableName, ErrInvalidTableName)
	}

	query := fmt.Sprintf(`SELECT 
		column_name as field,
		data_type || 
		CASE 
			WHEN data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR') THEN '(' || data_length || ')'
			WHEN data_type = 'NUMBER' AND data_precision IS NOT NULL THEN 
				'(' || data_precision || 
				CASE WHEN data_scale IS NOT NULL AND data_scale > 0 THEN ',' || data_scale ELSE '' END || ')'
			ELSE ''
		END as type,
		CASE WHEN nullable = 'Y' THEN 'YES' ELSE 'NO' END as null_flag,
		CASE 
			WHEN column_name IN (SELECT column_name FROM user_cons_columns WHERE constraint_name IN 
				(SELECT constraint_name FROM user_constraints WHERE table_name = '%s' AND constraint_type = 'P'))
			THEN 'PRI'
			ELSE ''
		END as key_flag,
		data_default as default_value,
		'' as extra
	FROM user_tab_columns 
	WHERE table_name = '%s' 
	ORDER BY column_id`, strings.ToUpper(tableName), strings.ToUpper(tableName))
	
	rows, err := db.conn.Query(query)
	if err != nil {
		db.logger.WithError(err).WithField("table_name", tableName).Error("执行查询表结构失败")
		return nil, NewTableError("describe_table", tableName, err)
	}
	defer rows.Close()

	var columns []ColumnInfo
	for rows.Next() {
		var col ColumnInfo
		var defaultValue sql.NullString
		
		if err := rows.Scan(&col.Field, &col.Type, &col.Null, &col.Key, &defaultValue, &col.Extra); err != nil {
			db.logger.WithError(err).WithField("table_name", tableName).Error("扫描列信息失败")
			return nil, NewTableError("scan_column_info", tableName, err)
		}
		
		if defaultValue.Valid {
			col.Default = defaultValue.String
		} else {
			col.Default = "NULL"
		}
		
		columns = append(columns, col)
	}

	if err := rows.Err(); err != nil {
		db.logger.WithError(err).WithField("table_name", tableName).Error("遍历列信息失败")
		return nil, NewTableError("iterate_columns", tableName, err)
	}

	db.logger.WithFields(map[string]interface{}{
		"table_name":   tableName,
		"column_count": len(columns),
	}).Info("成功获取表结构信息")

	return columns, nil
}

// ExecuteQuery 执行SQL查询并返回格式化的结果
func (db *DB) ExecuteQuery(query string, limit int) (string, error) {
	return db.ExecuteQueryWithTimeout(context.Background(), query, limit, 30*time.Second)
}

// ExecuteQueryWithTimeout 带超时的SQL查询执行
func (db *DB) ExecuteQueryWithTimeout(ctx context.Context, query string, limit int, timeout time.Duration) (string, error) {
	// 基本的SQL注入防护
	if !db.isSafeQuery(query) {
		return "", fmt.Errorf("不安全的查询语句")
	}

	// 记录原始查询用于调试
	db.logger.WithFields(map[string]interface{}{
		"原始查询": query,
		"限制": limit,
	}).Debug("执行查询")

	// 添加ROWNUM限制（如果查询是SELECT且没有ROWNUM限制）
	if isSelectQuery(query) && !hasRowNumClause(query) && limit > 0 {
		// Oracle使用ROWNUM而不是LIMIT
		cleanQuery := strings.TrimSuffix(strings.TrimSpace(query), ";")
		query = fmt.Sprintf("SELECT * FROM (%s) WHERE ROWNUM <= %d", cleanQuery, limit)
		db.logger.WithField("最终查询", query).Debug("添加ROWNUM限制")
	}

	// 使用连接池管理器执行带超时的查询
	rows, err := db.poolManager.ExecuteWithTimeout(ctx, query, timeout)
	if err != nil {
		db.logger.WithFields(map[string]interface{}{
			"错误": err,
			"查询": query,
		}).Error("查询执行失败")
		return "", fmt.Errorf("查询执行失败: %w", err)
	}
	defer rows.Close()

	// 获取列信息
	columns, err := rows.Columns()
	if err != nil {
		return "", fmt.Errorf("获取列信息失败: %w", err)
	}

	// 准备结果字符串
	var result strings.Builder
	
	// 写入列标题
	result.WriteString(strings.Join(columns, "\t"))
	result.WriteString("\n")
	result.WriteString(strings.Repeat("-", len(columns)*15))
	result.WriteString("\n")

	// 准备扫描目标
	values := make([]interface{}, len(columns))
	valuePtrs := make([]interface{}, len(columns))
	for i := range values {
		valuePtrs[i] = &values[i]
	}

	rowCount := 0
	// 遍历结果行
	for rows.Next() {
		if err := rows.Scan(valuePtrs...); err != nil {
			return "", fmt.Errorf("扫描结果失败: %w", err)
		}

		// 格式化行数据
		var rowData []string
		for _, val := range values {
			if val == nil {
				rowData = append(rowData, "NULL")
			} else {
				switch v := val.(type) {
				case []byte:
					rowData = append(rowData, string(v))
				case time.Time:
					rowData = append(rowData, v.Format("2006-01-02 15:04:05"))
				default:
					rowData = append(rowData, fmt.Sprintf("%v", v))
				}
			}
		}
		
		result.WriteString(strings.Join(rowData, "\t"))
		result.WriteString("\n")
		rowCount++
	}

	if err := rows.Err(); err != nil {
		return "", fmt.Errorf("遍历结果失败: %w", err)
	}

	// 添加结果统计
	result.WriteString(fmt.Sprintf("\n查询完成，返回 %d 行记录", rowCount))

	return result.String(), nil
}

// isValidTableName 验证表名是否有效（防止SQL注入）
func isValidTableName(tableName string) bool {
	if len(tableName) == 0 || len(tableName) > 64 {
		return false
	}
	
	// 只允许字母、数字、下划线和美元符号
	for _, char := range tableName {
		if !((char >= 'a' && char <= 'z') ||
			(char >= 'A' && char <= 'Z') ||
			(char >= '0' && char <= '9') ||
			char == '_' || char == '$') {
			return false
		}
	}
	
	return true
}

// isSafeQuery 基本的SQL安全检查
// isSafeQuery 检查查询是否安全（支持配置控制）
func (db *DB) isSafeQuery(query string) bool {
	// 如果配置中禁用了安全检查，直接返回true
	if db.config != nil && !db.config.Security.EnableSafetyCheck {
		db.logger.Debug("安全检查已禁用，允许执行查询")
		return true
	}
	
	db.logger.Debug("安全检查已启用，正在检查查询安全性")
	
	query = strings.ToUpper(strings.TrimSpace(query))
	
	// 禁止的关键字
	dangerousKeywords := []string{
		"DROP", "DELETE", "INSERT", "UPDATE", "ALTER", "CREATE",
		"TRUNCATE", "REPLACE", "GRANT", "REVOKE", "SET", "CALL",
	}
	
	for _, keyword := range dangerousKeywords {
		if strings.Contains(query, keyword) {
			db.logger.WithFields(map[string]interface{}{
				"query": query,
				"keyword": keyword,
			}).Warn("检测到不安全的查询语句，拒绝执行")
			return false
		}
	}
	
	db.logger.Debug("查询通过安全检查")
	return true
}

// isSelectQuery 检查是否为SELECT查询
func isSelectQuery(query string) bool {
	query = strings.ToUpper(strings.TrimSpace(query))
	return strings.HasPrefix(query, "SELECT") || strings.HasPrefix(query, "SHOW") || strings.HasPrefix(query, "DESCRIBE")
}

// hasLimitClause 检查查询是否已包含LIMIT子句
func hasLimitClause(query string) bool {
	query = strings.ToUpper(query)
	return strings.Contains(query, "LIMIT")
}

// hasRowNumClause 检查查询是否已包含ROWNUM限制
func hasRowNumClause(query string) bool {
	query = strings.ToUpper(query)
	return strings.Contains(query, "ROWNUM")
}